using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DTO;
using System.Globalization;

namespace DAO
{
    public class KT_SOTIETKIEM_DAO
    {
        public DataTable TraCuu(string sql)
        {
            DataProvider dp = new DataProvider();
            DataTable dt;
            dt = dp.ExecuteQuery(sql);
            return dt;
        }


        public int LayMaSoTietKiem()
        {
            DataProvider dp = new DataProvider();
            string sql = "Select Max(MS) From SOTIETKIEM";
            DataTable dt = dp.ExecuteQuery(sql);
            int kq = int.Parse (dt.Rows[0][0].ToString());
            return kq;
        }

        public DataTable LoadDSLoaiTietKiem()
        {
            DataProvider dp = new DataProvider();
            string sql = "Select * From LOAITIETKIEM";
            DataTable dt = dp.ExecuteQuery(sql);
            return dt;
        }

        public Double LayLaiSuat(int MLTK)
        {
            DataProvider dp = new DataProvider();
            string sql = "Select LaiSuat From LOAITIETKIEM Where MLTK = " + MLTK;
            DataTable dt = dp.ExecuteQuery(sql);
            double  kq = double.Parse(dt.Rows[0][0].ToString());
            return kq;
        }

        public void ThemMoiSoTietKiem(KT_SOTIETKIEM_DTO stk)
        {
            DataProvider dp = new DataProvider();
            string sql;
            if(stk .NgayHetHan.CompareTo (stk.NgayMoSo) > 0)
                sql = "Insert into SOTIETKIEM(MLTK,TenKhachHang,GioiTinh,DiaChi,DienThoai,CMND,SoTienHienCo,NgayMoSo,NgayHetHan,TinhTrang,LaiSuat) " +
                       "values(" + stk.MLTK + ", '" + stk.TenKhachHang + "', " + stk.GioiTinh + ", '" + stk.DiaChi + "', '" + stk.DienThoai + "', '" + stk.CMND + "', " +
                       +stk.SoTienHienCo + ", '" + stk.NgayMoSo.ToShortDateString () +"', '" + stk.NgayHetHan.ToShortDateString() + "', 1, " + stk.LaiSuat + ")";
            else
                sql = "Insert into SOTIETKIEM(MLTK,TenKhachHang,GioiTinh,DiaChi,DienThoai,CMND,SoTienHienCo,NgayMoSo,TinhTrang,LaiSuat) " +
                       "values(" + stk.MLTK + ", '" + stk.TenKhachHang + "', " + stk.GioiTinh + ", '" + stk.DiaChi + "', '" + stk.DienThoai + "', '" + stk.CMND + "', " +
                       +stk.SoTienHienCo + ", '" + stk.NgayMoSo.ToShortDateString () +"', 1, " + stk.LaiSuat + ")";
            dp.ExecuteNonQuery(sql);
        }

        public void CapNhatSoTietKiem(KT_SOTIETKIEM_DTO stk)
        {
            DataProvider dp = new DataProvider();
            string sql;
            if (stk.NgayHetHan.CompareTo(stk.NgayMoSo) > 0)
                sql = "Update SOTIETKIEM Set MLTK = " + stk.MLTK + ", TenKhachHang = '" + stk.TenKhachHang + "', GioiTinh = " + stk.GioiTinh + ", DiaChi = '" + stk.DiaChi + "', " +
                    "DienThoai = '" + stk.DienThoai + "', CMND = '" + stk.CMND + "', SoTienHienCo = " + stk.SoTienHienCo + ", NgayHetHan = '" + stk.NgayHetHan + "' " +
                    " Where MS = " + stk.MS;
            else
                sql = "Update SOTIETKIEM Set MLTK = " + stk.MLTK + ", TenKhachHang = '" + stk.TenKhachHang + "', GioiTinh = " + stk.GioiTinh + ", DiaChi = '" + stk.DiaChi + "', " +
                    "DienThoai = '" + stk.DienThoai + "', CMND = '" + stk.CMND + "', SoTienHienCo = " + stk.SoTienHienCo + ", NgayHetHan = NULL " +
                    " Where MS = " + stk.MS;
            dp.ExecuteNonQuery(sql);
        }
        public DataTable TraCuuSoTietKiem_K(string sql)
        {
            DataProvider dp = new DataProvider();
            DataTable dt = dp.ExecuteQuery(sql);
            return dt;
        }

        public void XoaMotSoTietKiem(int ms)
        {
            DataProvider dp = new DataProvider();
            string sql = "Delete From SOTIETKIEM Where MS = " + ms.ToString();
            dp.ExecuteNonQuery(sql);
        }

        public int LayTenLoaiTietKiem(int ma)
        {
            DataProvider dp = new DataProvider();
            string sql = "Select TenLoaiTietKiem From LOAITIETKIEM Where MLTK = " + ma;
            DataTable dt = dp.ExecuteQuery(sql);
            return int.Parse(dt.Rows[0]["TenLoaiTietKiem"].ToString());
        }
          
#region BAO CAO
        public DataTable BaoCaoLayTongThuTrongNgay(DateTime ngay)
        {
            int ng = ngay.Day;
            int th = ngay.Month;
            int n = ngay.Year;
            DataTable dt;
            DataProvider dp = new DataProvider();
            string sql = "Select ltk.TenLoaiTietKiem ,sum (pgt.SoTienGui) as Tong_Thu";
            sql +=" From SOTIETKIEM stk, LOAITIETKIEM ltk, PHIEUGUITIEN pgt";
            sql += " Where stk.MLTK = ltk.MLTK and stk.MS = pgt.MS and year(pgt.NgayGui)=" + n.ToString() + " and month(pgt.NgayGui)=" + th.ToString()+" and day(pgt.NgayGui)=" +ng.ToString();
            sql += " Group by ltk.TenLoaiTietKiem";
            dt = dp.ExecuteQuery(sql);
            return dt;
        }
        public DataTable BaoCaoLayTongChiTrongNgay(DateTime ngay)
        {
            int ng = ngay.Day;
            int thang = ngay.Month;
            int nam = ngay.Year;
            DataTable dt;
            DataProvider dp = new DataProvider();
            string sql = "Select ltk.TenLoaiTietKiem, sum (prt.SoTienRut) as Tong_Chi";
            sql += " From SOTIETKIEM stk, LOAITIETKIEM ltk, PHIEURUTTIEN prt";
            sql += " Where stk.MLTK = ltk.MLTK and stk.MS = prt.MS and day(prt.NgayRut)=" + ng.ToString() + " and month(prt.NgayRut)=" + thang.ToString() + "and year(prt.NgayRut)=" + nam.ToString();
            sql += " Group by ltk.TenLoaiTietKiem";
            dt = dp.ExecuteQuery(sql);
            return dt;
        }
        public DataTable BaoCaoMoSoThang(int mltk, int thang,int nam)
        {
            DataTable dt;
            DataProvider dp = new DataProvider();
            string sql = "Select NgayMoSo as Ngay, count (*) as So_Mo";
            sql += " From SOTIETKIEM";
            sql += " Where MLTK=" + mltk.ToString() + " and month(NgayMoSo)=" + thang.ToString()+" and year(NgayMoSo)="+nam.ToString();
            sql += " Group by NgayMoSo";
            dt = dp.ExecuteQuery(sql);
            return dt;
        }
        public DataTable BaoCaoDongSoThang(int mltk, int thang, int nam)
        {
            DataTable dt;
            DataProvider dp = new DataProvider();
            string sql = "Select NgayDongSo as Ngay, count (*) as So_Dong";
            sql += " From SOTIETKIEM";
            sql += " Where MLTK=" + mltk.ToString() + "and month(NgayMoSo)=" + thang.ToString()+" and year(NgayDongSo)="+ nam.ToString();
            sql += " Group by NgayDongSo";
            dt = dp.ExecuteQuery(sql);
            return dt;
        }
#endregion

    }

}
    
   
  
   